﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using MixScript.DBService;


namespace MixScript.Appl.Lib
{
    public class cSearchHelp : cDBService
    {

        #region Property
        private bool inited;

        public bool bInited
        {
            get { return inited; }
            set { inited = value; }
        }

        private string[] arrfild;
        public string[] arrField
        {
            get { return arrfild; }
            set { arrfild = value; }
        }

        private string searchid;
        public string Searchid
        {
            get { return searchid; }
            set { searchid = value; }
        }

        private DataTable dtsh;
        public DataTable dtSrcHlp
        {
            get { return dtsh; }
            set { dtsh = value; }
        }

        private int[] indexkey;

        public int[] indexKey
        {
            get { return indexkey; }
            set { indexkey = value; }
        }
        private int[] indexdesc;

        public int[] indexDesc
        {
            get { return indexdesc; }
            set { indexdesc = value; }
        }
        private string sdelimited;

        public string sDelimited
        {
            get { return sdelimited; }
            set { sdelimited = value; }
        }

        private string stblnm;

        public string sTableName
        {
            get { return stblnm; }
            set { stblnm = value; }
        }

        #endregion

        public cSearchHelp()
        {

        }



        public cSearchHelp(string srchid)
        {
            try
            {
                string msg = "";
                if (getArrfield(srchid))
                {
                    getIndexKeyDesc(ref indexkey, ref indexdesc);
                    getDelimiter(ref sdelimited);
                    getTableName(dtSrcHlp, ref stblnm);
                    bInited = true;

                }
                else
                {
                    throw new Exception(msg);
                }

            }
            catch (Exception ex)
            {

                throw ex;
            }
        }


        public void getDATASQL(string ssql, ref DataTable dt)
        {
            try
            {
                dt= new DataTable();
                dt = Dbserver.ExecuteQuery(ssql, "dtview");

            }
            catch (Exception ex)
            {

                throw;
            }
        }

        #region Get Key Index, Desc Index and Delimiter

        public bool getDelimiter(ref string delimiter)
        {
            if (dtsh.Rows.Count > 0)
            {
                delimiter = dtsh.Rows[0]["delimited"].ToString();
                return true;
            }
            else
            {
                delimiter = " ";
                return false;

            }
        }
        public void getIndexKeyDesc(ref int[] idxKey, ref int[] idxDesc)
        {
            getIndexKey(ref idxKey);
            getIndexDesc(ref idxDesc);
        }
        private bool getIndexKey(ref int[] i)
        {
            return getIndexKeyDesc(ref i, "iskey");
        }
        private bool getIndexDesc(ref int[] i)
        {
            return getIndexKeyDesc(ref i, "isdesc");
        }

        private bool getIndexKeyDesc(DataTable dtArrField, ref int[] i, string sflnm)
        {
            try
            {
                bool b;
                List<int> ix = new List<int>();
                if (dtArrField.Rows.Count > 0)
                {
                    foreach (DataRow dr in dtsh.Rows)
                    {
                        if (dr[sflnm].ToString() == "1")
                        {
                            ix.Add(dtsh.Rows.IndexOf(dr));
                        }
                    }

                    if (ix.Count > 0)
                    {
                        b = true;
                        i = ix.ToArray();
                    }
                    else
                    {
                        i = new int[] { 0 };
                    }
                }
                return true;
            }
            catch (Exception ex)
            {

                throw;
            }
        }
        private bool getIndexKeyDesc(ref int[] i, string sflnm)
        {
            //List<int> ix = new List<int>();
            //bool b;
            //if (dtsh.Rows.Count > 0)
            //{
            //    foreach (DataRow dr in dtsh.Rows)
            //    {
            //        if (dr[sflnm].ToString() == "1")
            //        {
            //            ix.Add(dtsh.Rows.IndexOf(dr));
            //        }
            //    }

            //    if (ix.Count > 0)
            //    {
            //        b = true;
            //        i = ix.ToArray();
            //    }
            //    else
            //    {
            //        i = new int[] { 0 };
            //    }
            //}
            //return true;
            return getIndexKeyDesc(dtsh, ref i, sflnm);
        }
        #endregion
        private bool getTableName(string srcid)
        {
            return getTableName(srcid, ref this.stblnm);
        }
        private bool getTableName(string srcid, ref string stablename)
        {
            try
            {

                if (getSearchHelp(srcid))
                {
                    stablename = dtSrcHlp.Rows[0]["tablename"].ToString();
                    return true;
                }
                else
                {
                    stablename = "";
                    return false;
                }

            }
            catch (Exception ex)
            {

                throw ex;
            }
        }
        private bool getTableName(DataTable dtAffFld, ref string stablename)
        {
            if (dtAffFld.Rows.Count > 0)
            {
                stablename = dtAffFld.Rows[0]["tablename"].ToString();
                return true;
            }
            else
            {
                return false;
            }

        }
        private bool getSearchHelp(string srcid)
        {
            try
            {
                return getSearchHelp(srcid, ref dtsh);
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }
        private bool getSearchHelp(string srcid, ref DataTable dt)
        {
            try
            {
                dt = new DataTable();
                string sql = "select * from  vsy_searchhelp where searchid='" + srcid + "' order by seqno";
                dt = Dbserver.ExecuteQuery(sql, "Searchhelp");
                if (dt.Rows.Count > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }


            }
            catch (Exception ex)
            {

                throw ex;
            }
        }
        private bool getArrfield(string srcid, ref string[] sfld, out string msg)
        {
            try
            {
                msg = "";
                dtsh = new DataTable();
                List<string> ls = new List<string>();
                if (srcid.Trim() != string.Empty)
                {
                    //  string sql = "select * from  vsy_searchhelp where searchid='" + srcid + "'";
                    //dtsh = Dbserver.ExecuteQuery(sql, "Searchhelp");

                    if (getSearchHelp(srcid))
                    {

                        foreach (DataRow dr in dtsh.Rows)
                        {
                            if (dr["fieldname"].ToString() != string.Empty)
                            {
                                ls.Add(dr["fieldname"].ToString());
                            }
                            //sfld = new string[] { };
                            //    sfld = ls.ToArray<string>();

                            //   setStatusMessage(s[0] + s[1], MessageType.Normal);

                        }

                        sfld = ls.ToArray();

                        return true;
                    }
                    else
                    {
                        msg = "Search Help Not Found!";

                        return false;
                    }
                }
                else
                {
                    msg = "Search ID not initialized";
                    return false;

                }


            }
            catch (Exception ex)
            {

                throw ex;
            }
        }
        private bool getArrfield(string srcid)
        {
            try
            {
                string[] ss = new string[] { };
                string msg;
                bool b = getArrfield(srcid, ref ss, out  msg);
                if (b)
                {
                    arrField = ss;
                    return b;
                }
                else
                {
                    throw new Exception(msg);
                }


            }
            catch (Exception ex)
            {

                throw ex;
            }
        }
        
        private string genWhereStatement(string stext, string[] sfld)
        {

            try
            {
                string ss = "where ";
                foreach (string s in sfld)
                {
                    ss += s + " like '%" + stext + "%' or ";
                }

                ss = ss.Substring(0, ss.Length - 4);
                return ss;

            }
            catch (Exception ex)
            {

                throw;
            }
        }
        private string genQueryLimit(string stbname, string[] sfld, string sSearch, string flname, string sparam)
        {
            try
            {
                string sfield = "";
                string sql = "select ";
                foreach (string s in sfld)
                {
                    sfield += s + ",";
                }
                sfield = sfield.Substring(0, sfield.Length - 1);


                if (ServerProvider.ToLower() == "sqlserver")
                {
                    sql += "top 100 " + sfield + " from " + stbname + " ";
                    if (sSearch.Trim() != string.Empty)
                    {
                        sql += genWhereStatement(sSearch, sfld);
                        if(flname.Trim()!=string.Empty)
                        sql += " and " + flname + "='" + sparam + "' ";
                        //sql += " " + swhere;
                    }
                    else
                    {
                        if (flname.Trim() != string.Empty)
                        sql += " where 1=1 and " + flname + "='" + sparam + "' " ;
                    }

                    sql += " order by " + sfld[0].ToString();
                }

                else if (ServerProvider.ToLower() == "mysql")
                {
                    sql += sfield + " from  " + stbname + " ";

                    if (sSearch.Trim() != string.Empty)
                    {
                        sql += genWhereStatement(sSearch, sfld);
                    }
                    sql += " and " + flname + "='" + sparam + "' ";
                    sql += " order by " + sfld[0].ToString() + " limit 1000";


                }

                return sql;





            }
            catch (Exception ex)
            {

                throw new Exception(ex.Message);
            }
        }

        private string genQuery(string stbname, string[] sfld, string sSearch)
        {
            try
            {
                string sfield = "";
                string sql = "select ";
                foreach (string s in sfld)
                {
                    sfield += s + ",";
                }
                sfield = sfield.Substring(0, sfield.Length - 1);


                if (ServerProvider.ToLower() == "sqlserver")
                {
                    sql += "top 100 " + sfield + " from " + stbname + " ";
                    if (sSearch.Trim() != string.Empty)
                    {
                        sql += genWhereStatement(sSearch, sfld);
                    }

                    sql += " order by " + sfld[0].ToString();
                }

                else if (ServerProvider.ToLower() == "mysql")
                {
                    sql += sfield + " from  " + stbname + " ";

                    if (sSearch.Trim() != string.Empty)
                    {
                        sql += genWhereStatement(sSearch, sfld);
                    }

                    sql += " order by " + sfld[0].ToString() + " limit 1000";


                }

                return sql;





            }
            catch (Exception ex)
            {

                throw new Exception(ex.Message);
            }
        }
        public bool SearchText(ref DataTable dt, string tablename, string[] fieldname, string sSearch)
        {
            try
            {

                //  DataTable dt = new DataTable();
                dt = Dbserver.ExecuteQuery(genQuery(tablename, fieldname, sSearch), tablename);
                //gv.DataSource = dt;
                //gv.ClearSelection();
                if (dt.Rows.Count > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }


            }
            catch (Exception ex)
            {

                throw new Exception(this.GetType() + " " + ex.Message);
            }
        }

      
        public bool SearchTextGRN(ref DataTable dt, string tablename, string[] fieldname, string sSearch,string sdate,string stype)
        {
            try
            {

                //  DataTable dt = new DataTable();
                string ssql = genQuery(tablename, fieldname, sSearch);
                if (sdate != string.Empty)
                {

                    if (stype == "2")
                    {
                        ssql = "select top 100 IORFNO,ITNO,ITNM,IOQT,UNMSNS,RGDT from vw_srcgrn   where (IORFNO like '%" + sSearch + "%' or ITNO like '%" + sSearch + "%' or ITNM like '%" + sSearch + "%' or IOQT like '%" + sSearch + "%' or UNMSNS like '%" + sSearch + "%')  and convert(varchar(10),rgdt,105 )='" + sdate + "' and itcl='31' order by rgdt desc";
                        //ssql = ssql.Replace("order by IORFNO", "") + " " + genWhereStatement("", fieldname) + " and convert(varchar(10),rgdt,110)='" + sdate + "' order by rgdt desc";
                    }
                    else
                    {
                        ssql = "select top 100 IORFNO,ITNO,ITNM,IOQT,UNMSNS,RGDT from vw_srcgrn   where (IORFNO like '%" + sSearch + "%' or ITNO like '%" + sSearch + "%' or ITNM like '%" + sSearch + "%' or IOQT like '%" + sSearch + "%' or UNMSNS like '%" + sSearch + "%')  and convert(varchar(10),rgdt,105 )='" + sdate + "' order by rgdt desc";
                    }
                }
                else
                {
                    if (stype == "2")
                    {
                        ssql = "select top 100 IORFNO,ITNO,ITNM,IOQT,UNMSNS,RGDT from vw_srcgrn   where IORFNO like '%" + sSearch + "%' or ITNO like '%" + sSearch + "%' or ITNM like '%" + sSearch + "%' or IOQT like '%" + sSearch + "%' or UNMSNS like '%" + sSearch + "%'  and itcl<>'31' order by rgdt desc";
                        //ssql = ssql.Replace("order by IORFNO", "") + " " + genWhereStatement("", fieldname) + " order by rgdt desc";
                    }
                    else
                    {
                        ssql = "select top 100 IORFNO,ITNO,ITNM,IOQT,UNMSNS,RGDT from vw_srcgrn   where IORFNO like '%" + sSearch + "%' or ITNO like '%" + sSearch + "%' or ITNM like '%" + sSearch + "%' or IOQT like '%" + sSearch + "%' or UNMSNS like '%" + sSearch + "%'  order by rgdt desc";
                    }
                }
                
                dt = Dbserver.ExecuteQuery(ssql, tablename);
                //gv.DataSource = dt;
                //gv.ClearSelection();
                if (dt.Rows.Count > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }


            }
            catch (Exception ex)
            {

                throw new Exception(this.GetType() + " " + ex.Message);
            }
        }

        public bool SearchTextLimitPlus(ref DataTable dt, string tablename, string[] fieldname, string sSearch, string flname, string sparam,string swhere)
        {
            try
            {

                //  DataTable dt = new DataTable();
                string ssql=genQueryLimit(tablename, fieldname, sSearch, flname, sparam);
                if (ssql.ToUpper().Contains(" WHERE "))
                {
                    ssql = ssql.Replace("order by",  swhere + " order by ");
                }
                else
                {
                    ssql = ssql.Replace("order by", " where 1=1 " + swhere + " order by ");
                }

                
                dt = Dbserver.ExecuteQuery(ssql, tablename);
                //gv.DataSource = dt;
                //gv.ClearSelection();
                if (dt.Rows.Count > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }


            }
            catch (Exception ex)
            {

                throw new Exception(this.GetType() + " " + ex.Message);
            }
        }
        public bool SearchTextLimit(ref DataTable dt, string tablename, string[] fieldname, string sSearch, string flname, string sparam)
        {
            try
            {

                //  DataTable dt = new DataTable();
                dt = Dbserver.ExecuteQuery(genQueryLimit(tablename, fieldname, sSearch,flname,sparam), tablename);
                //gv.DataSource = dt;
                //gv.ClearSelection();
                if (dt.Rows.Count > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }


            }
            catch (Exception ex)
            {

                throw new Exception(this.GetType() + " " + ex.Message);
            }
        }

        public bool fillgridlimitplus(ref DataTable dt, string tablename, string[] fieldname, string flname, string sparam, string swhere)
        {
            try
            {

                //   DataTable dt = new DataTable();
                dt = Dbserver.ExecuteQuery(genQueryLimit(tablename, fieldname, "", flname, sparam).Replace("order by",swhere+ " order by"), tablename);

                if (dt.Rows.Count > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {

                throw new Exception(ex.Message);
            }
        }


        public bool FillGridLimit(ref DataTable dt, string tablename, string[] fieldname, string flname, string sparam)
        {
            try
            {

                //   DataTable dt = new DataTable();
                string ssql = genQueryLimit(tablename, fieldname, "", flname, sparam);
                dt = Dbserver.ExecuteQuery(ssql, tablename);

                if (dt.Rows.Count > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {

                throw new Exception(ex.Message);
            }
        }

        
        public bool FillGrid(ref DataTable dt, string tablename, string[] fieldname)
        {
            try
            {

                //   DataTable dt = new DataTable();
                  
                string ssql=genQuery(tablename, fieldname, "");
                ssql=ssql.Replace("order by IORFNO","order by RGDT desc");


                dt = Dbserver.ExecuteQuery(ssql, tablename);

                if (dt.Rows.Count > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {

                throw new Exception(ex.Message);
            }
        }
    }
}
